package id.net.iconpln.bb.server.dao;

import id.net.iconpln.bb.server.util.CommonModule;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import oracle.jdbc.OracleTypes;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.extjs.gxt.ui.client.Registry;

@Repository
public class PerencanaanDao {
	@Autowired	private SessionFactory sessionFactory;
		
	public List<Map> getListKontrakByKit(String kode, Map map) {
		// TODO Auto-generated method stub
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        try {
            String sql = null;
            CallableStatement call;
//            if (kode.equals("PEMBANGKITBYKONTRAK")){
            if (kode.equals("KONTRAKBYKIT")){
                sql = "{call BBTRXPENGADAAN.SETID_PLTU(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_pltu").toString()); 
                call.execute();
            }
            sql = "{? = call BBTRXPENGADAAN.GETLISTPENGADAANBYKIT(?)}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.CURSOR);
            call.setString(2, kode);
            call.execute();
            
            ResultSet rset = (ResultSet) call.getObject(1);
            lMap = CommonModule.convertResultsetToListLower(rset);
        }catch (Exception e) {
			e.printStackTrace();
			map.clear();
            map.put("message", e.getMessage());  

		}

        return lMap;  
            
	}
	public List<Map> getListRencanaByKit(String kode, Map map) {
		// TODO Auto-generated method stub
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        try {
            String sql = null;
            CallableStatement call;           
            
            if (kode.equals("RKAP")){
            sql = "{call BBTRXPERENCANAAN.SETTAHUN(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("tahun").toString()); 
            call.execute();
            
            }else if (kode.equals("TAHUNRENCPRO")){
            	System.out.println(map.get("tahun").toString() + " " + map.get("id_pltu").toString() );
                sql = "{call BBTRXPERENCANAAN.SETTAHUN(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("tahun").toString()); 
                call.execute();
            	
                sql = "{call BBTRXPERENCANAAN.SETID_PLTU(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_pltu").toString()); 
                call.execute();
            }else if (kode.equals("CEKUNIT")){
                sql = "{call BBTRXPERENCANAAN.SETTAHUN(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("tahun").toString()); 
                call.execute();
            	
                sql = "{call BBTRXPERENCANAAN.SETID_PLTU(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_pltu").toString()); 
                call.execute();

                sql = "{call BBTRXPERENCANAAN.SETID_UNIT(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_unit").toString()); 
                call.execute();
            	
                sql = "{call BBTRXPERENCANAAN.SETNO_RKAP(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("no_rkap").toString()); 
                call.execute();

                
            }
            sql = "{call BBTRXPERENCANAAN.SETID_PLTU(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("id_pltu").toString()); 
            call.execute();

//            --}else if(kode.equals("LISTRENC"))
            
            sql = "{? = call BBTRXPERENCANAAN.GETLISTRENCANABYKIT(?)}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.CURSOR);
            call.setString(2, kode);
            call.execute();
            
            ResultSet rset = (ResultSet) call.getObject(1);
            lMap = CommonModule.convertResultsetToListLower(rset);
        }catch (Exception e) {
			e.printStackTrace();
			map.clear();
            map.put("message", e.getMessage());  

		}

        return lMap;                   

	}

	public List<Map> getFormulaProduksi(String kode, Map map) {
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        try {
            String sql = null;
            CallableStatement call;
            
            
            sql = "{? = call BBTRXPERENCANAAN.GETFORMULAPRODUKSI(?,?,?,?)}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.CURSOR);
            call.setDouble(2, Double.parseDouble(map.get("dt").toString()));
            call.setDouble(3, Double.parseDouble(map.get("cf").toString()));
            call.setDouble(4, Double.parseDouble(map.get("sfc").toString()));
            call.setDouble(5, Double.parseDouble(map.get("jp").toString()));
            call.execute();
            
            ResultSet rset = (ResultSet) call.getObject(1);
            lMap = CommonModule.convertResultsetToListLower(rset);
        }catch (Exception e) {
			e.printStackTrace();
			map.clear();
            map.put("message", e.getMessage());  

		}

        return lMap;          
		
	}
	public List<Map> crudPerencanaan(String kode, Map map) {
		// TODO Auto-generated method stub
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        try {
            String sql = null;
            CallableStatement call;                       
            
//            TAHUN,ID_PLTU,,ID_URAIAN,SATUAN
            if (map.get("crud").toString().equals("CREATE")){
            
            sql = "{call BBTRXPERENCANAAN.SETTAHUN(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("tahun").toString()); 
            call.execute();
            
            
            sql = "{call BBTRXPERENCANAAN.SETID_PLTU(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("id_pltu").toString()); 
            call.execute();

            sql = "{call BBTRXPERENCANAAN.SETID_UNIT(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("id_unit").toString()); 
            call.execute();
            
            sql = "{call BBTRXPERENCANAAN.SETNO_RKAP(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("no_rkap").toString()); 
            call.execute();
            
            
            sql = "{call BBTRXPERENCANAAN.SETCREATEBY(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("createby").toString());
            call.execute();
            
            }else if (map.get("crud").toString().equals("RKAP")){
            	System.out.println(map.get("tahun").toString() + " " + map.get("id_pltu").toString() );
                
            	sql = "{call BBTRXPERENCANAAN.SETTAHUN(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("tahun").toString()); 
                call.execute();
                
                
                sql = "{call BBTRXPERENCANAAN.SETID_PLTU(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_pltu").toString()); 
                call.execute();
            	
            	sql = "{call BBTRXPERENCANAAN.SETCREATEBY(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("createby").toString());
                call.execute();
                
                
            }else if (map.get("crud").toString().equals("UPDATE")){
            	sql = "{call BBTRXPERENCANAAN.SETBULAN(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("bulan").toString()); 
                call.execute();
                
            	
            	sql = "{call BBTRXPERENCANAAN.SETTAHUN(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("tahun").toString()); 
                call.execute();
                
                sql = "{call BBTRXPERENCANAAN.SETNO_RKAP(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("no_rkap").toString()); 
                call.execute();

                sql = "{call BBTRXPERENCANAAN.SETDT(?)}";
                call = con.prepareCall(sql);
                call.setDouble(1, Double.parseDouble(map.get("dt").toString())); 
                call.execute();
                
                sql = "{call BBTRXPERENCANAAN.SETCF(?)}";
                call = con.prepareCall(sql);
                call.setDouble(1, Double.parseDouble(map.get("cf").toString())); 
                call.execute();
                
                sql = "{call BBTRXPERENCANAAN.SETJP(?)}";
                call = con.prepareCall(sql);
                call.setDouble(1, Double.parseDouble( map.get("jp").toString())); 
                call.execute();
                
                sql = "{call BBTRXPERENCANAAN.SETSFC(?)}";
                call = con.prepareCall(sql);
                call.setDouble(1, Double.parseDouble(map.get("sfc").toString())); 
                call.execute();
                
                
                sql = "{call BBTRXPERENCANAAN.SETID_PLTU(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_pltu").toString()); 
                call.execute();
            	
                sql = "{call BBTRXPERENCANAAN.SETID_UNIT(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_unit").toString()); 
                call.execute();
                               
                sql = "{call BBTRXPERENCANAAN.SETID_URAIAN(?)}";
                call = con.prepareCall(sql);
                call.setString(1, "3"); 
                call.execute();
                
                sql = "{call BBTRXPERENCANAAN.SETID_URAIAN(?)}";
                call = con.prepareCall(sql);
                call.setString(1, "19"); 
                call.execute();
                
                sql = "{call BBTRXPERENCANAAN.SETID_URAIAN(?)}";
                call = con.prepareCall(sql);
                call.setString(1, "22"); 
                call.execute();
                
                sql = "{call BBTRXPERENCANAAN.SETID_URAIAN(?)}";
                call = con.prepareCall(sql);
                call.setString(1, "24"); 
                call.execute();
                
                sql = "{call BBTRXPERENCANAAN.SETID_URAIAN(?)}";
                call = con.prepareCall(sql);
                call.setString(1, "35"); 
                call.execute();
                
                sql = "{call BBTRXPERENCANAAN.SETID_URAIAN(?)}";
                call = con.prepareCall(sql);
                call.setString(1, "27"); 
                call.execute();
            }
            
            
            sql = "{call BBTRXPERENCANAAN.SETAPP_ACCESS(?)}";
            call = con.prepareCall(sql);
            call.setString(1, "WEB");
            call.execute();
            
            sql = "{? = call BBTRXPERENCANAAN.CRUDPERENCANAAN(?)}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.VARCHAR);
            call.setString(2, map.get("crud").toString());
            call.execute();
            
            
            
//            ResultSet rset = (ResultSet) call.getObject(1);
//            lMap = CommonModule.convertResultsetToListLower(rset);
            
//            map.clear();
            map.put("message", call.getString(1));
            System.out.println("no rkap"  + call.getString(1).substring(4));
        }catch (Exception e) {
			e.printStackTrace();
			map.clear();
            map.put("message", e.getMessage());  

		}
        lMap.add(map);
        return lMap;
	}
	
	public List<Map> crudPengadaan(String kode, Map map) {
		// TODO Auto-generated method stub
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        try {
        	
            String sql = null;
            CallableStatement call;       
           
            if (map.get("crud").equals("CREATEAMANDEMEN")){
                sql = "{call BBTRXPENGADAAN.SETNO_PJBB(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("no_pjbb").toString());
                call.execute();
                
                sql = "{call BBTRXPENGADAAN.SETTGL_AMANDEMEN(?)}";
                call = con.prepareCall(sql);
                DateFormat dfa = new SimpleDateFormat("dd/mm/yyyy");
                Date tgla = dfa.parse(map.get("tgl_amandemen").toString());
                call.setDate(1, new java.sql.Date(tgla.getTime()));
                call.execute();
            }
            
            sql = "{call BBTRXPENGADAAN.SETTGL_SELESAI_PJBB(?)}";
            call = con.prepareCall(sql);
            DateFormat df = new SimpleDateFormat("dd/mm/yyyy");
            Date tgl = df.parse(map.get("akhir_berlaku").toString());
            call.setDate(1, new java.sql.Date(tgl.getTime()));
            call.execute();
            
            sql = "{call BBTRXPENGADAAN.SETID_PEMASOK(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("id_pemasok").toString());
            call.execute();
            
            sql = "{call BBTRXPENGADAAN.SETID_JENIS_KONTRAK(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("id_jenis_kontrak").toString());
            call.execute();
            
            sql = "{call BBTRXPENGADAAN.SETID_PLTU(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("id_pltu").toString());
            call.execute();
            
            sql = "{call BBTRXPENGADAAN.SETKEB_PERTAHUN(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("kebutuhan_tahun").toString());
            call.execute();
            
            sql = "{call BBTRXPENGADAAN.SETLOT(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("lot").toString());
            call.execute();
            
            sql = "{call BBTRXPENGADAAN.SETVOL_PJBB(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("vol_pjbb").toString());
            call.execute();
            
            sql = "{call BBTRXPENGADAAN.SETVOL_PJBB_TAHUN(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("vol_pjbb_thn").toString());
            call.execute();

            
            sql = "{call BBTRXPENGADAAN.SETHARGA_RP_TON(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("hrg_ton").toString());
            call.execute();
            
            sql = "{call BBTRXPENGADAAN.SETHARGA_PENYESUAIAN(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("hrg_sesuai").toString());
            call.execute();

            
            sql = "{call BBTRXPENGADAAN.SETRP_NILAI_KONTRAK(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("nilai_kontrak").toString());
            call.execute();
            
            sql = "{call BBTRXPENGADAAN.SETKALORI(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("kalori").toString());
            call.execute();
            
        	sql = "{call BBTRXPENGADAAN.SETTGL_PJBB(?)}";
            call = con.prepareCall(sql);
            DateFormat dfs = new SimpleDateFormat("dd/mm/yyyy");
            Date tgls = dfs.parse(map.get("awal_berlaku").toString());
            call.setDate(1, new java.sql.Date(tgls.getTime()));
            call.execute();
            
        	sql = "{call BBTRXPENGADAAN.SETKETERANGAN(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("ket").toString());
            call.execute();

        	sql = "{call BBTRXPENGADAAN.SETJENIS_PENGIRIMAN(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("jenis_kapal").toString());
            call.execute();

        	sql = "{call BBTRXPENGADAAN.SETJAMINAN_BANK(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("jaminan_bank").toString());
            call.execute();
    		
        	sql = "{call BBTRXPENGADAAN.SETCREATEBY(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("createby").toString());
            call.execute();
            
        	sql = "{call BBTRXPENGADAAN.SETAPP_ACCESS(?)}";
            call = con.prepareCall(sql);
            call.setString(1, "WEB");
            call.execute();
            
            sql = "{? = call BBTRXPENGADAAN.CRUDPENGADAAN(?)}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.VARCHAR);
            call.setString(2, map.get("crud").toString());
            call.execute();
            
            map.clear();
            map.put("message", call.getString(1));
            System.out.println("pjbb"  + call.getString(1));
        }catch (Exception e) {
			e.printStackTrace();
			map.clear();
            map.put("message", e.getMessage());  

		}
        lMap.add(map);
        return lMap;
	}
}
